﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Collections;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using FileHelpers;
using FileHelpers.DataLink;
using System.Net;
using System.IO;
using FtpLib;
using System.Data.SqlClient;
using System.Security.Cryptography;

namespace CDRtoSQL
{
    
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }

        #region Классы импорта статистики

        //описание формата правильных, нужных нам строк
        [DelimitedRecord(",")]
        public class ImportCDRData
        {
            public long unix_time;
            public long call_id;
            public long cdr_type;
            public long leg_type;
            [FieldQuoted()]
            public string h323_conf_id;
            [FieldQuoted()]
            public string peer_address;
            [FieldQuoted()]
            public string peer_sub_address;
            [FieldQuoted()]
            public string h323_setup_time;
            [FieldQuoted()]
            public string alert_time;
            [FieldQuoted()]
            public string h323_connect_time;
            [FieldQuoted()]
            public string h323_disconnect_time;
            [FieldQuoted()]
            public string h323_disconnect_cause;
            [FieldQuoted()]
            public string disconnect_text;
            [FieldQuoted()]
            public string h323_call_origin;
            public long charged_units;
            [FieldQuoted()]
            public string info_type;
            public long paks_out;
            public long bytes_out;
            public long paks_in;
            public long bytes_in;
            [FieldQuoted()]
            public string username;
            [FieldQuoted()]
            public string clid;
            [FieldQuoted()]
            public string dnis;
            [FieldIgnored()]
            public string trash23;//это и далее поля нам не нужны
            [FieldIgnored()]
            public string trash24;
            [FieldIgnored()]
            public string trash25;
            [FieldIgnored()]
            public string trash26;
            [FieldIgnored()]
            public string trash27;
            [FieldIgnored()]
            public string trash28;
            [FieldIgnored()]
            public string trash29;
            [FieldIgnored()]
            public string trash30;
            [FieldIgnored()]
            public string trash31;
            [FieldIgnored()]
            public string trash32;
            [FieldIgnored()]
            public string trash33;
            [FieldIgnored()]
            public string trash34;

        }

        //описание формата неправильных строк, игнорируются при импорте
        [FixedLengthRecord()]
        public class ImportCDRDataShort
        {
            [FieldFixedLength(10)]
            public long unix_time;
        }

        #endregion

        #region Методы для чтения файла статистики

        private object[] ReadCDRFile(string fileName)
        {

            object[] res;
            MultiRecordEngine engine = new MultiRecordEngine(typeof(ImportCDRData), typeof(ImportCDRDataShort));
            engine.RecordSelector = new RecordTypeSelector(CustomSelector);

            res = engine.ReadFile(fileName);

            res = res.Where(val => val.GetType().ToString() == "CDRtoSQL.Form2+ImportCDRData").ToArray(); //убираем пустые элементы массива

            foreach (ImportCDRData cdrstr in res) //в цикле исправляем даты на понятные базе данных
            {
                cdrstr.h323_connect_time = DateTime.Parse(TransformDateFormat(cdrstr.h323_connect_time)).ToString();
                cdrstr.h323_disconnect_time = DateTime.Parse(TransformDateFormat(cdrstr.h323_disconnect_time)).ToString();
            }
            engine.Close();
            return res;
        }

        Type CustomSelector(MultiRecordEngine engine, string record)
        {
            if (record.Length > 11)
                return typeof(ImportCDRData);
            else
                return typeof(ImportCDRDataShort);
        }

        #endregion

        
        public class DataForSQL
        {
            public DateTime call_start;
            public DateTime call_end;
            public string call_from;
            public string call_to;
            public string call_id;
            public string call_text;
            public string file_md5sum;
            public string call_idnum;
        }

        public string TransformDateFormat(string transformDate) //данный метод преобразует дату из файла в формат, удобный для вставки в базу данных.
        {
            string[] transformDateArray = transformDate.Split(' ');
            transformDateArray[0] = transformDateArray[0].Remove(8);
            string readyDate = transformDateArray[2] + ", " + transformDateArray[4] + " " + transformDateArray[3] + " " + transformDateArray[5] + " " + transformDateArray[0];
            return readyDate;
        }

        static double ConvertToUnixTimestamp(DateTime date)
        {
            DateTime origin = new DateTime(1970, 1, 1, 0, 0, 0, 0);
            TimeSpan diff = date - origin;
            return Math.Floor(diff.TotalSeconds);
        }

        static DateTime ConvertFromUnixTimestamp(double timestamp)
        {
            DateTime origin = new DateTime(1970, 1, 1, 0, 0, 0, 0);
            return origin.AddSeconds(timestamp);
        }

        private DataForSQL fillReturnString(ImportCDRData sourceData, string md5hash)
        {
            DataForSQL resultData = new DataForSQL();
            resultData.call_from = sourceData.clid.ToString().TrimStart('7');
            resultData.call_to = sourceData.dnis.ToString();
            resultData.call_start = DateTime.Parse(sourceData.h323_connect_time);
            resultData.call_end = DateTime.Parse(sourceData.h323_disconnect_time);
            resultData.call_id = sourceData.h323_conf_id;
            resultData.call_text = sourceData.disconnect_text;
            resultData.file_md5sum = md5hash;
            resultData.call_idnum = sourceData.call_id.ToString();
            return resultData;
        }


        public bool checkFileImport(string filename, string md5sum)
        {
            //ArrayList dataToCount = new ArrayList();
            string sQuery = "SELECT * FROM [CDRStats].[dbo].[UploadedFiles] WHERE filename=\'" + filename + "\' AND md5sum=\'" + md5sum + "\'";
            using (SqlConnection sqlConn = new SqlConnection("Server=" + Properties.Settings.Default.db_hostname + ";Database=" + Properties.Settings.Default.db_name + ";Trusted_Connection=True;"))
            {
                SqlCommand sqlQuery = new SqlCommand(sQuery, sqlConn);
                sqlConn.Open();
                SqlDataReader reader = sqlQuery.ExecuteReader();

                return reader.HasRows;
            }
        }

        public bool checkFileImport(string filename)
        {
            //ArrayList dataToCount = new ArrayList();
            string sQuery = "SELECT * FROM [CDRStats].[dbo].[UploadedFiles] WHERE filename=\'" + filename + "\'";
            using (SqlConnection sqlConn = new SqlConnection("Server=" + Properties.Settings.Default.db_hostname + ";Database=" + Properties.Settings.Default.db_name + ";Trusted_Connection=True;"))
            {
                SqlCommand sqlQuery = new SqlCommand(sQuery, sqlConn);
                sqlConn.Open();
                SqlDataReader reader = sqlQuery.ExecuteReader();

                return reader.HasRows;
            }
        }
        
        private ArrayList prepareDataForSQL(object[] res, string fname) //метод разбирает массив объектов и считает время звонков в возвращаемый массив
        {
            string md5fhash = GetMD5HashFromFile(fname);
            ArrayList readyData = new ArrayList();
            for (int i = 0; i < res.Count(); i++) readyData.Add(fillReturnString((ImportCDRData)res[i], md5fhash));
            return readyData;
        }

        private void InsertIntoSQL(DataForSQL str)
        {
            using (SqlConnection sqlConn = new SqlConnection("Server=" + Properties.Settings.Default.db_hostname + ";Database=" + Properties.Settings.Default.db_name + ";Trusted_Connection=True;"))
            {
                SqlCommand sqlQuery = new SqlCommand("INSERT INTO CDRStat (call_start, call_end, call_from, call_to, call_id, call_idnum, call_text, file_md5sum) VALUES " +
                "(@call_start, @call_end, @call_from, @call_to, @call_id, @call_idnum, @call_text, @file_md5sum)", sqlConn);
                sqlQuery.Parameters.AddWithValue("@call_start", ConvertToUnixTimestamp(str.call_start));
                sqlQuery.Parameters.AddWithValue("@call_end", ConvertToUnixTimestamp(str.call_end));
                sqlQuery.Parameters.AddWithValue("@call_from", str.call_from);
                sqlQuery.Parameters.AddWithValue("@call_to", str.call_to);
                sqlQuery.Parameters.AddWithValue("@call_id", str.call_id);
                sqlQuery.Parameters.AddWithValue("@call_idnum", str.call_idnum);
                sqlQuery.Parameters.AddWithValue("@call_text", str.call_text);
                sqlQuery.Parameters.AddWithValue("@file_md5sum", str.file_md5sum);
                try
                {
                    sqlConn.Open();
                    sqlQuery.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
        }

        private void CleanupOLDData(string file_md5sum)
        {
            using (SqlConnection sqlConn = new SqlConnection("Server=" + Properties.Settings.Default.db_hostname + ";Database=" + Properties.Settings.Default.db_name + ";Trusted_Connection=True;"))
            {
                SqlCommand sqlQuery = new SqlCommand("DELETE FROM CDRStat " +
                "WHERE file_md5sum=@file_md5sum", sqlConn);
                sqlQuery.Parameters.AddWithValue("@file_md5sum", file_md5sum);
                try
                {
                    sqlConn.Open();
                    sqlQuery.ExecuteNonQuery();
                    sqlConn.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
        }

        private void CleanupOLDFile(string file_name)
        {
            using (SqlConnection sqlConn = new SqlConnection("Server=" + Properties.Settings.Default.db_hostname + ";Database=" + Properties.Settings.Default.db_name + ";Trusted_Connection=True;"))
            {
                SqlCommand sqlQuery = new SqlCommand("DELETE FROM UploadedFiles " +
                "WHERE filename=@file_name", sqlConn);
                sqlQuery.Parameters.AddWithValue("@file_name", file_name);
                try
                {
                    sqlConn.Open();
                    sqlQuery.ExecuteNonQuery();
                    sqlConn.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
        }

        private void InsertIntoSQLUploadedFile(string filename, string md5sum)
        {
            using (SqlConnection sqlConn = new SqlConnection("Server=" + Properties.Settings.Default.db_hostname + ";Database=" + Properties.Settings.Default.db_name + ";Trusted_Connection=True;"))
            {
                SqlCommand sqlQuery = new SqlCommand("INSERT INTO UploadedFiles (filename, md5sum, loaddate) VALUES " +
                "(@filename, @md5sum, @loaddate)", sqlConn);
                sqlQuery.Parameters.AddWithValue("@filename", filename);
                sqlQuery.Parameters.AddWithValue("@md5sum", md5sum);
                sqlQuery.Parameters.AddWithValue("@loaddate", ConvertToUnixTimestamp(DateTime.Now));
                try
                {
                    sqlConn.Open();
                    sqlQuery.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
        }

        private string GetMD5HashFromFile(string fileName)
        {
            FileStream file = new FileStream(fileName, FileMode.Open);
            MD5 md5 = new MD5CryptoServiceProvider();
            byte[] retVal = md5.ComputeHash(file);
            file.Close();
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < retVal.Length; i++)
            {
                sb.Append(retVal[i].ToString("x2"));
            }
            return sb.ToString();
        }

        

        private void Form2_Load(object sender, EventArgs e)
        {
            try
            {
                FtpConnection ftpEl = new FtpConnection(Properties.Settings.Default.ftp_hostname, Properties.Settings.Default.ftp_username, Properties.Settings.Default.ftp_password);
                ftpEl.Open();
                ftpEl.Login();
                foreach (var filen in ftpEl.GetFiles("el-*"))
                {
                    checkedListBox1.Items.Add(filen.Name);
                }
                ftpEl.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Не удалось загрузить данные с FTP сервера.\n" + ex.ToString());
            }
        }
        private void button2_Click(object sender, EventArgs e)
        {
            
            int importedFileCount = 0; //счетчик новых файлов
            int deniedFileCount = 0; //счетчик уже загруженных файлов
            int replacedFileCount = 0; //счетчик замененных файлов

            if (checkedListBox1.CheckedItems.Count > 0)
            {
                FtpConnection ftpEl = new FtpConnection(Properties.Settings.Default.ftp_hostname, Properties.Settings.Default.ftp_username, Properties.Settings.Default.ftp_password);
                ftpEl.Open();
                ftpEl.Login();
                
                for (int i = 0; i < checkedListBox1.CheckedItems.Count; i++)
                {
                    ftpEl.GetFile(checkedListBox1.CheckedItems[i].ToString(), false);
                    string md5s = GetMD5HashFromFile(checkedListBox1.CheckedItems[i].ToString());

                    if (!checkFileImport(checkedListBox1.CheckedItems[i].ToString(), md5s))
                    {
                        if (checkFileImport(checkedListBox1.CheckedItems[i].ToString()))
                        {
                            CleanupOLDData(md5s);
                            CleanupOLDFile(checkedListBox1.CheckedItems[i].ToString());
                            replacedFileCount++;
                        }
                        object[] filestr = ReadCDRFile(checkedListBox1.CheckedItems[i].ToString());
                        if (filestr.Count() > 0)
                        {
                            ArrayList res = prepareDataForSQL(filestr, checkedListBox1.CheckedItems[i].ToString());
                            foreach (DataForSQL str in res)
                            {
                                InsertIntoSQL(str);

                            }
                            InsertIntoSQLUploadedFile(checkedListBox1.CheckedItems[i].ToString(), md5s);
                            importedFileCount++;
                        }
                    }
                    else deniedFileCount++;

                }
                MessageBox.Show("Импортировано файлов: " + importedFileCount + ". Заменено файлов: " +replacedFileCount+ ". Пропущено файлов: "+ deniedFileCount + ".");
                ftpEl.Close();
                for (int i = 0; i < checkedListBox1.CheckedItems.Count; i++) File.Delete(checkedListBox1.CheckedItems[i].ToString());
            }
            else MessageBox.Show("Не выбраны файлы для импорта");
        }

        private void button3_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < checkedListBox1.Items.Count; i++)
            {
                checkedListBox1.SetItemChecked(i, true);
                checkedListBox1.Update();
            }
        }

    }
}
